﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;

namespace BookingManagementSystem.Models
{
    public static class EventDataAccess
    {
        private enum ColumnsGetBookingsByRange
        {
            UniqueId,
            CheckIn,
            CheckOut,
            RoomColorName,
            Description
            
        }

        private static OleDbConnection GetConnection()
        {
            var dataDir = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();
            return new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\\db.mdb", dataDir));
        }
        public static List<BookingCalendarModel> GetBookingsByRange(DateTime from, DateTime to)
        {
            var bookings = new List<BookingCalendarModel>();
            using (OleDbConnection connection = GetConnection())
            {
                string commandString = GetBookingsByRangeSQlCommand();
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandString;
                    command.CommandType = CommandType.Text;
                    command.Parameters.AddWithValue("@StartDate", System.Data.OleDb.OleDbType.Date).Value = from;
                    command.Parameters.AddWithValue("@EndDate", System.Data.OleDb.OleDbType.Date).Value = to;
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            bookings.Add(new BookingCalendarModel()
                            {
                                UniqueId = reader.GetInt32((int)ColumnsGetBookingsByRange.UniqueId),
                                CheckIn = reader.GetDateTime((int)ColumnsGetBookingsByRange.CheckIn),
                                CheckOut = reader.GetDateTime((int)ColumnsGetBookingsByRange.CheckOut),
                                Description = reader.GetValue((int)ColumnsGetBookingsByRange.Description) == DBNull.Value ? null : reader.GetString((int)ColumnsGetBookingsByRange.Description),
                                Color = reader.GetValue((int)ColumnsGetBookingsByRange.RoomColorName) == DBNull.Value ? null : reader.GetString((int)ColumnsGetBookingsByRange.RoomColorName),
                            });
                        }
                    }

                }
            }
            return bookings;

        }

        private static string GetBookingsByRangeSQlCommand()
        {
            return @"  select 
                        t_booking.booking_id
                        ,t_booking.booking_checkin
                        ,t_booking.booking_checkout
                        ,t_room.room_color
                        ,t_room.room_desc

                        from 

                        t_booking 
                        left join t_room
                        on t_booking.room_id=t_room.room_id
                where 

            (((t_booking.booking_checkin >= @StartDate) AND (t_booking.booking_checkin <= @EndDate)) 
            OR ((t_booking.booking_checkout >= @StartDate) AND (t_booking.booking_checkout <= @EndDate))
            OR ((t_booking.booking_checkin < @StartDate) AND (t_booking.booking_checkout > @EndDate)))
                
                
                
                ";
        }
    }
}